﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using KPIS.DBM;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Novacode;

namespace KPIS.GERP.GHRMS.APP.Classes
{
    public class MailMerge
    {
        public bool MergeTemplate(string strTemplateFilePath, string strSaveAsFilePath
            , Dictionary<string, string> fields
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , List<Dictionary<string, string>> Dics = null
            , Dictionary<string, bool> areas = null)
        {
            bool bOK = false;

            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                try
                {
                    if (fields != null)
                    {
                        foreach (var field in fields)
                        {
                            document.ReplaceText("«" + field.Key + "»", field.Value);
                        }
                    }

                    if (dtbs != null)
                    {
                        foreach (var _dtb in dtbs)
                        {
                            System.Data.DataTable dtb = _dtb.Value;
                            for (int i = 0; i < dtb.Rows.Count; i++)
                            {
                                System.Data.DataRow dr = dtb.Rows[i];

                                foreach (System.Data.DataColumn col in dtb.Columns)
                                {
                                    var ColumnName = col.ColumnName + (i + 1).ToString();
                                    string strValue = dr[ColumnName].ToString();

                                    document.ReplaceText("«" + ColumnName + "»", strValue);
                                }
                            }
                        }
                    }

                    if (Dics != null && Dics.Count > 0)
                    {
                        for (int i = 0; i < Dics.Count; i++)
                        {
                            var Dic = Dics[i];

                            foreach (var DicItem in Dic)
                            {
                                string ColumnName = DicItem.Key + (i + 1).ToString();
                                string strValue = DicItem.Value;

                                document.ReplaceText("«" + ColumnName + "»", strValue);
                            }
                        }
                    }

                    if (areas != null)
                    {
                    }

                    while (document.Text.IndexOf("«") >= 0)
                    {
                        int Left = document.Text.IndexOf("«");
                        int Right = document.Text.IndexOf("»");

                        string strText = document.Text.Substring(Left, Right - Left + 1);
                        document.ReplaceText(strText, "");
                    }

                    document.SaveAs(strSaveAsFilePath);
                    bOK = true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                }
            }

            return bOK;
        }

        public void SaveFileToClient(HttpResponse response, string strFilePath, string strFileName, bool bDeleteFile)
        {
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
            response.WriteFile(strFilePath + strFileName);
            response.Flush();
            if (bDeleteFile)
            {
                System.IO.File.Delete(strFilePath + strFileName);
            }
            response.End();
        }

        public bool MergeExcel(HttpResponse response, string strTemplateFilePath, string strFileName
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , Dictionary<string, string> Dics = null
            //, Dictionary<string, Dictionary<string, string>> DicDic = null
            , OfficeOpenXml.Table.TableStyles tableStyle = OfficeOpenXml.Table.TableStyles.None)
        {
            bool bOK = false;

            FileInfo file = new FileInfo(strTemplateFilePath);

            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                if (pck.Workbook.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets[1];

                    if (dtbs != null)
                    {
                        foreach (var dtb in dtbs)
                        {
                            if (dtb.Value.Rows.Count > 0)
                            {
                                if (dtb.Key.IndexOf('!') >= 0)
                                {
                                    string strSheetName = dtb.Key.Split('!')[0];
                                    string strCell = dtb.Key.Split('!')[1];

                                    ws = pck.Workbook.Worksheets[strSheetName];

                                    if (ws == null)
                                    {
                                        pck.Workbook.Worksheets.Add(strSheetName);
                                        ws = pck.Workbook.Worksheets[strSheetName];
                                    }

                                    ws.Cells[strCell].LoadFromDataTable(dtb.Value, false, tableStyle);
                                }
                                else
                                {
                                    ws.Cells[dtb.Key].LoadFromDataTable(dtb.Value, false, tableStyle);
                                }
                            }
                        }
                    }

                    if (Dics != null)
                    {
                        foreach (var Dic in Dics)
                        {
                            if (Dic.Key.IndexOf('!') >= 0)
                            {
                                string strSheetName = Dic.Key.Split('!')[0];
                                string strCell = Dic.Key.Split('!')[1];

                                var ws2 = pck.Workbook.Worksheets[strSheetName];

                                if (ws2 == null)
                                {
                                    pck.Workbook.Worksheets.Add(strSheetName);
                                    ws2 = pck.Workbook.Worksheets[strSheetName];
                                }

                                ws2.Cells[strCell].Value = Dic.Value;
                            }
                            else
                            {
                                ws.Cells[Dic.Key].Value = Dic.Value;
                            }
                        }
                    }

                    //if (DicDic != null)
                    //{
                    //    foreach (var Dics2 in DicDic)
                    //    {
                    //        ExcelWorksheet ws2 = pck.Workbook.Worksheets.Add(Dics2.Key);

                    //        foreach (var Dic in Dics2.Value)
                    //        {
                    //            if (Dic.Key.IndexOf('!') >= 0)
                    //            {
                    //                string strSheetName = Dic.Key.Split('!')[0];
                    //                string strCell = Dic.Key.Split('!')[1];

                    //                ws2 = pck.Workbook.Worksheets[strSheetName];
                    //                ws2.Cells[strCell].Value = Dic.Value;
                    //            }
                    //            else
                    //            {
                    //                ws2.Cells[Dic.Key].Value = Dic.Value;
                    //            }
                    //        }
                    //    }
                    //}

                    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)));
                    response.BinaryWrite(pck.GetAsByteArray());
                    pck.Dispose();
                }
            }
            return bOK;
        }


        #region[Export Excel]
        public void WriteDicToExcel(HttpResponse Response, Dictionary<string, string> _dtM, string type, string path)
        {
            if (_dtM.Count <= 0) return;
            CheckFolderExist(path);

            FileInfo newFile = new FileInfo(Path.Combine(path, type + "Template.xlsx"));
            if (!newFile.Exists) return;

            using (ExcelPackage xlPackage = new ExcelPackage(newFile, true))
            {
                //////you can customize this method to do your excel
                if (_dtM.Count > 0)
                    AddWorkSheet(_dtM, "M", ':', xlPackage);

                //////write file excel
                WriteFile(Response, type, xlPackage);
            }
        }

        private static void AddWorkSheet(Dictionary<string, string> _dtM, string _sheet, char _char, ExcelPackage xlPackage)
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[_sheet];
            if (worksheet != null)
            {
                foreach (KeyValuePair<string, string> item in _dtM)
                {
                    string[] _cell = item.Key.Split(_char);
                    if (_cell.Length >= 2)
                    {
                        int row = Convert.ToInt32(_cell[0]);
                        int col = Convert.ToInt32(_cell[1]);
                        worksheet.Cells[row, col].Value = item.Value;
                        worksheet.Cells[row, col].Style.Font.SetFromFont(new System.Drawing.Font("TH Sarabun New", 14, FontStyle.Regular));
                        worksheet.Cells[row, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    }
                }
            }
        }

        private void WriteFile(HttpResponse Response, string type, ExcelPackage xlPackage)
        {
            //you can customize file name
            string filename = type + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;  filename=" + filename);
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.BinaryWrite(xlPackage.GetAsByteArray());
            Response.Flush();
            Response.End();
        }

        private void CheckFolderExist(string path)
        {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
                Main main = new Main();
                main.FolderACL("Everyone", path);
            }
        }
        #endregion[Export Excel]
    }
}